﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Data.SqlTypes;
/**
 * ApmetrixDBInterface
 * Note: limit access control for this class to C#'s package-private Equiv
 */
namespace Apmetrix
{
    class ApmetrixDBInterface
    {
        //Test Variable
        bool TESTING = true;

        //SQL CE Variables
        private SqlCeConnection connection;
        private SqlCeEngine engine;
        private SqlCeDataReader urlReader;

        private readonly string TAG = "Apmetrix SDK / DBInterface";
        private readonly int TIMER_STOPPED = 0;
        private readonly int TIMER_RUNNING = 1;

        //Session Table data;
        private int lastSessionID;
        private long lastSessionLastTS;
        private long initialSessionTS;
        private int lowestSession;

        // urls table data
        private int id;
        private string url;
        private int session;

        // timer table data
        private long eventDuration;

        // joined table data
        private int highestSession;

        private string dbName;
        private string source;
        private int sessionCount;
        private int packetCount;

        private static DateTime MiliToDate(long ticks)
        {
            return new DateTime(long.Parse(ticks.ToString()));
        }

        /**
         * Create the local database using the name specified
         * For testing purposes if the DB is already created delete it
         * before creating a new one.
         * 
         * @param dbName name of the database file
         */
        public ApmetrixDBInterface(string dbName)
        {


            this.dbName = dbName;
            this.source = dbName + ".sdf";

            if (File.Exists(source))
            {
                Console.WriteLine("{0} Failed to create databse: " + dbName + " already exists", TAG);
                //File.Delete(source);

            }
            else
            {
                engine = new SqlCeEngine("Data Source = " + source);
                engine.CreateDatabase();
                

            }

        }

        /**
         * open a database that will be used for reading and writing.
         * @return Status.OK if no error occurred, else Status.ERROR
         */
        public int open()
        {
            //init return;
            int status = ApmetrixConstants.SQLCE_OK;

            try
            {
                if (connection != null)
                {
                    connection.Close();
                    connection = null;
                }
                //Get the connection handle to the database, set the commands
                connection = SQLCEHelper();
            }
            catch (SqlCeException se)
            {
                status = ApmetrixConstants.SQLCE_ERROR;
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0} Failed to Open Database: " + dbName, TAG);
            }

            return status;
        }

        /**
         * close the database
         */
        public void close()
        {
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
                connection = null;
            }
        }

        //*******************HELPER FUNCTIONS*****************\\

        /// <summary>
        /// Checks the connection to the database. If its closed, opens it
        /// Should be called before any command is created to ensure the connection is open
        /// </summary>
        private void checkConnection()
        {
            if (connection.State == ConnectionState.Closed)
                connection.Open();
        }

        /// <summary>
        /// SQL CE Delete Helper function
        /// </summary>
        /// <param name="tablePar">Table to delete from</param>
        /// <param name="whereArgPar">Where Arg</param>
        /// <param name="whereClausePar">The specific value</param>
        /// <param name="statement">SQL Statement for errors</param>
        /// <returns></returns>
        private int sqlDelete(string tablePar, string whereArgPar, string whereClausePar, string statement, bool isInt)
        {
            checkConnection();

            SqlCeCommand cmd = connection.CreateCommand();

            int status = ApmetrixConstants.SQLCE_ERROR;

            cmd.CommandText = "DELETE FROM " + tablePar + " WHERE " + whereArgPar + " = ?";

            if (isInt)
            {
                cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
                cmd.Prepare();
                cmd.Parameters["p1"].Value = Convert.ToInt32(whereClausePar);
            }
            else
            {
                cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
                cmd.Prepare();
                cmd.Parameters["p1"].Value = whereClausePar;
            }






            try
            {
                cmd.ExecuteNonQuery();
                status = ApmetrixConstants.SQLCE_OK;
                if (ApmetrixConstants.LOG_DEBUG)
                {
                    Console.WriteLine("Row Deleted");
                    Console.WriteLine("SQL DELETE: {0}", statement);
                }

            }
            catch (SqlCeException sqlex)
            {
                status = ApmetrixConstants.SQLCE_ERROR;
                if (ApmetrixConstants.LOG_DEBUG)
                {
                    Console.WriteLine("SQL FAILED DELETE: {0}", statement);
                    Console.WriteLine("DeleteURLS: {0}", sqlex.Message);
                }
            }

            cmd.Parameters.Clear();

            return status;
        }


        //*******************END HELPER FUNCTIONS*****************\\


        /**
         * delete row from urls table for specified id <br>
         * Note: database must already be open in read/write mode
         * @see ApmetrixDBInterface#open()
         * @param id of the row to delete
         * @return SQLITE_OK or SQLITE_ERROR
         */
        public int deleteFromUrls(int idParam)
        {
            //		final String DELETE_URLS_FORMAT_SQL_STATEMENT =
            //			"DELETE FROM urls " +
            //			"WHERE _id=%i";

            //		String table = "urls";
            //		String whereClause = "id=" + id;
            //		String[] whereArgs = null;

            string DELETE_URLS_TABLE_FORMAT_SQL_STATEMENT = "DELETE FROM urls WHERE _id = " + idParam;

            return sqlDelete("urls", "_id", idParam.ToString(), DELETE_URLS_TABLE_FORMAT_SQL_STATEMENT, true);
        }




        /**
         * delete row from sessions table for specified session id <br>
         * Note: database must already be open in read/write mode
         * @see ApmetrixDBInterface#open()
         * @param sessionId id of row to delete
         * @return SQLITE_OK or SQLITE_ERROR
         */
        public int deleteLastSessionIdFromSessions(int sessionId)
        {
            //		final String DELETE_SESSIONS_FORMAT_SQL_STATEMENT = 
            //			"DELETE FROM sessions " +
            //			"WHERE lastSessionID=%i";

            //		String table = "sessions";
            //		String whereClause = "lastSessionID=" + sessionId;
            //		String[] whereArgs = null;

            string DELETE_SESSIONS_FORMAT_SQL_STATEMENT = "DELETE FROM sessions WHERE lastSessionID = " + sessionId;

            return sqlDelete("sessions", "lastSessionID", sessionId.ToString(), DELETE_SESSIONS_FORMAT_SQL_STATEMENT, true);
        }

        /**
         * insert or replace data in sessions table <br>
         * Note: database must already be open.  Logs status of insert operation.
         * @see ApmetrixDBInterface#open()
         * @param lastSessionID
         * @param lastSessionLastTS
         * @param initialSessionTS
         * @return SQLITE_OK or SQLITE_ERROR
         * 
         * Because Microsoft's SQL is a pile of trash
         * Query to see if the Entry exists if it does do a Delete then an insert 
         * If the entry does not exist do an insert
         * TODO: WRITE A SQLINSERT HELPER FUNCTION FOR THE LOVE OF GOD
         */
        public int insertOrReplaceSessions(int lastSessionID, long lastSessionLastTS, long initialSessionTS)
        {


            string table = "sessions";
            string whereArg = "lastSessionID";
            int status = 0;

            checkConnection();

            SqlCeCommand cmd = connection.CreateCommand();

            string DELETE_SESSIONS_TABLE_FORMAT_SQL_STATEMENT = "DELETE FROM sessions WHERE lastSessionID = " + lastSessionID;
            string INSERT_SESSIONS_FORMAT_SQL_STATEMENT = "INSERT INTO sessions (lastSessionID, lastSessionLastTS, initialSessionTS) VALUES (" + lastSessionID + "," + lastSessionLastTS + "," + initialSessionTS + ")";

            //Perform a Select Query to see if the Entry Exists, Then do a Delete/Insert (Replace)
            int result = 0;
            cmd.CommandText = "SELECT * FROM sessions WHERE lastSessionID = ?";
            cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
            cmd.Prepare();
            cmd.Parameters["p1"].Value = lastSessionID;

            //Try to find the entry, but if it doesnt exist our command is null
            //So catch the nullRef and set the result to 0 and just ya know move on with our life
            try
            {
                result = (int)cmd.ExecuteScalar();
            }
            catch (NullReferenceException sqlex)
            {
                result = 0;
            }
            catch (SqlCeException ce)
            {
                Console.WriteLine("{0} EXCEPTION: {1}", TAG, ce.Message);
            }

            cmd.Parameters.Clear();

            if (result > 0)
            {
                //Ghetto Replace = Delete Then Insert

                sqlDelete(table, whereArg, lastSessionID.ToString(), DELETE_SESSIONS_TABLE_FORMAT_SQL_STATEMENT, true);

                cmd.CommandText = "INSERT INTO sessions (lastSessionID, lastSessionLastTS, initialSessionTS) VALUES (?, ?, ?)";

                cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
                cmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.BigInt));
                cmd.Parameters.Add(new SqlCeParameter("p3", SqlDbType.BigInt));


                cmd.Prepare();

                cmd.Parameters["p1"].Value = lastSessionID;
                cmd.Parameters["p2"].Value = (lastSessionLastTS);
                cmd.Parameters["p3"].Value = (initialSessionTS);

                try
                {
                    cmd.ExecuteNonQuery();
                    status = ApmetrixConstants.SQLCE_OK;

                    if (ApmetrixConstants.LOG_DEBUG)
                    {
                        Console.WriteLine("Row Addaded");
                        Console.WriteLine("SQL Insert: {0}", INSERT_SESSIONS_FORMAT_SQL_STATEMENT);
                    }

                }
                catch (SqlCeException sqlex)
                {
                    status = ApmetrixConstants.SQLCE_ERROR;
                    if (ApmetrixConstants.LOG_DEBUG)
                    {
                        Console.WriteLine("InsertSessions: {0}", sqlex.Message);
                    }
                }

                cmd.Parameters.Clear();
                connection.Close();
            }
            ////If No entry, do an INSERT
            else
            {
                /*
                 * inserting into sessions table causes time to be updated to current timestamp
                 */
                cmd.CommandText = "INSERT INTO sessions (lastSessionID, lastSessionLastTS, initialSessionTS) VALUES (?, ?, ?)";

                cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
                cmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.BigInt));
                cmd.Parameters.Add(new SqlCeParameter("p3", SqlDbType.BigInt));


                cmd.Prepare();

                cmd.Parameters["p1"].Value = lastSessionID;
                cmd.Parameters["p2"].Value = (lastSessionLastTS);
                cmd.Parameters["p3"].Value = (initialSessionTS);

                try
                {
                    cmd.ExecuteNonQuery();
                    status = ApmetrixConstants.SQLCE_OK;

                    if (ApmetrixConstants.LOG_DEBUG)
                    {
                        Console.WriteLine("Row Addaded");
                        Console.WriteLine("SQL Insert: {0}", INSERT_SESSIONS_FORMAT_SQL_STATEMENT);
                    }

                }
                catch (SqlCeException sqlex)
                {
                    status = ApmetrixConstants.SQLCE_ERROR;
                    if (ApmetrixConstants.LOG_DEBUG)
                    {
                        Console.WriteLine("InsertSessions: {0}", sqlex.Message);
                    }
                }

                cmd.Parameters.Clear();
                connection.Close();
            }


            return status;
        }

        /**
         * insert data into urls table <br>
         * Note: database must already be open.  Logs status of insert operation
         * @see ApmetrixDBInterface#open()
         * @param url
         * @param session
         * @return SQLITE_OK or SQLITE_ERROR
         */
        public int insertUrls(string urlParam, int sessionParam)
        {
            /*
             * inserting into urls table causes id to be incremented automatically
             * and time to be updated to current timestamp
             */
            string INSERT_URLS_TABLE_FORMAT_SQL_STATEMENT =
                    "INSERT INTO urls " +
                    "(url, session) " +
                    "VALUES (" + urlParam + ", " + sessionParam + ")";

            int status = 0;

            checkConnection();

            SqlCeCommand cmd = connection.CreateCommand();

            cmd.CommandText = "INSERT INTO urls (url, session) VALUES (?, ?)";

            cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NText));
            cmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.Int));


            cmd.Prepare();

            cmd.Parameters["p1"].Value = urlParam;
            cmd.Parameters["p2"].Value = sessionParam;

            try
            {
                cmd.ExecuteNonQuery();
                status = ApmetrixConstants.SQLCE_OK;
                if (ApmetrixConstants.LOG_DEBUG)
                {
                    Console.WriteLine("Row Addaded");
                    Console.WriteLine("SQL Insert: {0}", INSERT_URLS_TABLE_FORMAT_SQL_STATEMENT);
                }

            }
            catch (SqlCeException sqlex)
            {
                status = ApmetrixConstants.SQLCE_ERROR;
                if (ApmetrixConstants.LOG_DEBUG)
                {
                    Console.WriteLine("SQL FAILED Insert: {0}", INSERT_URLS_TABLE_FORMAT_SQL_STATEMENT);
                    Console.WriteLine("insertUrls: {0}", sqlex.Message);
                }
            }

            cmd.Parameters.Clear();
            connection.Close();

            return status;
        }

        /**
          * Update the sessions table data for specified row <br>
          * Note: database must already be open. Logs status of update operation
          * @see ApmetrixDBInterface#open()
          * @param lastRequestTimestamp
          * @param sessionID
          * @return SQLITE_OK or SQLITE_ERROR
          */
        public int updateSessions(long lastRequestTimestamp, int sessionID)
        {
            string UPDATE_SESSIONS_TABLE_FORMAT_SQL_STATEMENT =
                    "UPDATE sessions " +
                    "SET lastSessionLastTS = " + lastRequestTimestamp +
                    "WHERE lastSessionID = " + sessionID;

            int status = 0;

            checkConnection();

            SqlCeCommand cmd = connection.CreateCommand();

            cmd.CommandText = "UPDATE sessions SET lastSessionLastTS = ? WHERE lastSessionID = ?";

            cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Float));
            cmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.Int));


            cmd.Prepare();

            cmd.Parameters["p1"].Value = lastRequestTimestamp;
            cmd.Parameters["p2"].Value = sessionID;

            try
            {
                cmd.ExecuteNonQuery();
                status = ApmetrixConstants.SQLCE_OK;
                if (ApmetrixConstants.LOG_DEBUG)
                {
                    Console.WriteLine("Row Updated");
                    Console.WriteLine("SQL UPDATE: {0}", UPDATE_SESSIONS_TABLE_FORMAT_SQL_STATEMENT);
                }

            }
            catch (SqlCeException sqlex)
            {
                status = ApmetrixConstants.SQLCE_ERROR;
                if (ApmetrixConstants.LOG_DEBUG)
                {
                    Console.WriteLine("SQL FAILED UPDATE: {0}", UPDATE_SESSIONS_TABLE_FORMAT_SQL_STATEMENT);
                    Console.WriteLine("UPDATE SESSION TS: {0}", sqlex.Message);
                }
            }

            cmd.Parameters.Clear();

            return status;
        }

        /**
         * Get next session data <br>
         * Note: use getters for lastSessionID, lastSessionLastTS and initialSessionTS data.  
         *   Database must already be open.
         * @see ApmetrixDBInterface#open()
         * @see ApmetrixDBInterface#getLastSessionID()
         * @see ApmetrixDBInterface#getLastSessionLastTS()
         * @see ApmetrixDBInterface#getInitialSessionTS()
         * @return SQLITE_OK or SQLITE_ERROR
         */
        public int selectNextSessionData()
        {

            //		final String SELECT_SESSIONS_SQL_STATEMENT = 
            //				"SELECT lastSessionID, lastSessionLastTS, initialSessionTS " +
            //				"FROM sessions " +
            //				"ORDER BY lastSessionID DESC " +
            //				"LIMIT 1";

            int status = ApmetrixConstants.SQLCE_ERROR;

            checkConnection();

            SqlCeCommand cmd = connection.CreateCommand();
            SqlCeDataReader reader;
            //Perform a Select Query to see if the Entry Exists
            cmd.CommandText = "SELECT TOP(1) lastSessionID, lastSessionLastTS, initialSessionTS  FROM sessions ORDER BY lastSessionID DESC";
            cmd.Prepare();

            //Try to find the entry, but if it doesnt exist our command is null
            //So catch the nullRef and set the result to 0 and just ya know move on with our life
            try
            {
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    lastSessionID = reader.GetInt32(0);
                    lastSessionLastTS = reader.GetInt64(1);
                    initialSessionTS = reader.GetInt64(2);

                }
                status = ApmetrixConstants.SQLCE_OK;
            }
            catch (NullReferenceException sqlex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0}: Woops, something went wrong. Check your arguements", TAG);
            }

            cmd.Parameters.Clear();

            return status;
        }


        /**
         * Get last sessionId from sessions table <br>
         * Note: use getter for lastSessionID data.  Database must already be open.
         * @see ApmetrixDBInterface#open()
         * @see ApmetrixDBInterface#getLastSessionID()
         * @return SQLITE_OK or SQLITE_ERROR	 
         */
        public int selectLastSessionIdFromSessions()
        {
            int status = ApmetrixConstants.SQLCE_ERROR;

            checkConnection();

            SqlCeCommand cmd = connection.CreateCommand();
            SqlCeDataReader reader;
            //Perform a Select Query to see if the Entry Exists
            cmd.CommandText = "SELECT TOP(1) lastSessionID FROM sessions ORDER BY lastSessionID";
            cmd.Prepare();

            //Try to find the entry, but if it doesnt exist our command is null
            //So catch the nullRef and set the result to 0 and just ya know move on with our life
            try
            {
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    lowestSession = reader.GetInt32(0);

                }
                status = ApmetrixConstants.SQLCE_OK;
            }
            catch (NullReferenceException sqlex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0}: selectLastSessionIdFromSessions: Woops, something went wrong. Check your arguements", TAG);
            }

            cmd.Parameters.Clear();

            return status;
        }

        /**
         * get row count for sessions table <br>
         * Note: database must already be open for read/write mode
         * @see ApmetrixDBInterface#open()
         * @return number of rows in sessions table
         */
        public int selectCountFromSessions()
        {
            //		final String SELECT_COUNT_SESSIONS_TABLE_SQL_STATEMENT =
            //				"SELECT count(lastSessionID) " +
            //				"FROM sessions";		
            int result = 0;

            checkConnection();

            SqlCeCommand cmd = connection.CreateCommand();
            //Perform a Select Query to get the count
            cmd.CommandText = "SELECT COUNT (lastSessionID) FROM sessions";
            cmd.Prepare();

            result = (int)cmd.ExecuteScalar();


            cmd.Parameters.Clear();

            return result;

        }

        /**
         * get row count for urls table <br>
         * Note: database must already be open for read/write mode
         * @see ApmetrixDBInterface#open()
         * @return number of rows in urls table
         */
        public int selectCountFromUrls()
        {
            //		final String SELECT_COUNT_URLS_TABLE_SQL_STATEMENT =
            //				"SELECT count(_id) " +
            //				"FROM urls";

            int result = 0;

            checkConnection();

            SqlCeCommand cmd = connection.CreateCommand();
            //Perform a Select Query to get the count
            cmd.CommandText = "SELECT COUNT (_id) FROM urls";
            cmd.Prepare();

            result = (int)cmd.ExecuteScalar();


            cmd.Parameters.Clear();

            return result;
        }

        /**
         * Get row count from sessions table and row count from urls table
         * <br>
         * Note: use getters for dbSessions and dbPackets data.  Database must already be open.
         * @see ApmetrixDBInterface#open()
         * @see ApmetrixDBInterface#getSessionCount()
         * @see ApmetrixDBInterface#getPacketCount()
         * @return SQLITE_OK or SQLITE_ERROR	 
         * 
         */
        public int selectCountFromSessionsAndUrls()
        {
            sessionCount = selectCountFromSessions();
            packetCount = selectCountFromUrls();

            return ApmetrixConstants.SQLCE_OK;
        }

        /**
         * Select first row of data from urls table <br>
         * Note: use getters for id, url and session data.  Database must already be open
         * @see ApmetrixDBInterface#open()
         * @see ApmetrixDBInterface#getID()
         * @see ApmetrixDBInterface#getUrl()
         * @see ApmetrixDBInterface#getSession()
         * @return SQLITE_OK or SQLITE_ERROR
         */
        public int selectUrl()
        {
            //		final String SELECT_URL_SQL_STATEMENT = 	
            //			"SELECT * " +
            //			"FROM urls " +
            //			"ORDER BY _id LIMIT 1";
            int status = ApmetrixConstants.SQLCE_ERROR;

            checkConnection();

            SqlCeCommand cmd = connection.CreateCommand();
            SqlCeDataReader reader;
            //Perform a Select Query to see if the Entry Exists
            cmd.CommandText = "SELECT TOP(1) _id, url, session  FROM urls ORDER BY _id";
            cmd.Prepare();

            //Try to find the entry, but if it doesnt exist our command is null
            //So catch the nullRef and set the result to 0 and just ya know move on with our life
            try
            {
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    id = reader.GetInt32(0);
                    url = reader.GetString(1);
                    session = reader.GetInt32(2);
                    Console.WriteLine(id);
                    Console.WriteLine(url);
                    Console.WriteLine(session);

                }
                status = ApmetrixConstants.SQLCE_OK;
            }
            catch (NullReferenceException sqlex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0}: SelectURL: Woops, something went wrong. Check your arguements", TAG);
            }

            cmd.Parameters.Clear();


            return status;
        }

        /**
         * Select all row data from urls table <br>
         * Note: use getter for urlReader.  It is up to the calling routine to 
         * open the database first and close cursor when finished with data
         * @see ApmetrixDBInterface#open()
         * @see ApmetrixDBInterface#getUrlsReader()
         * @return SQLITE_OK or SQLITE_ERROR
         */
        public int selectAllUrls()
        {
            //		final String SELECT_ALL_URLS_SQL_STATEMENT = 	
            //			"SELECT * " +
            //			"FROM urls " +
            //			"ORDER BY _id";
            int status = ApmetrixConstants.SQLCE_ERROR;

            checkConnection();

            SqlCeCommand cmd = connection.CreateCommand();

            //Perform a Select Query to see if the Entry Exists
            cmd.CommandText = "SELECT *  FROM urls ORDER BY _id";
            cmd.Prepare();

            //Try to find the entry, but if it doesnt exist our command is null
            //So catch the nullRef and set the result to 0 and just ya know move on with our life
            try
            {
                urlReader = cmd.ExecuteReader();
                status = ApmetrixConstants.SQLCE_OK;
            }
            catch (NullReferenceException sqlex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0}: SelectURL: Woops, something went wrong. Check your arguements", TAG);
            }

            cmd.Parameters.Clear();

            return status;
        }

        /**
         * Select session from joined tables <br>
         * Note: use getters for highestSession
         * @see ApmetrixDBInterface#getHighestSession()
         * @return SQLITE_OK or SQLITE_ERROR
         * Need to confirm functionality with Marc
         */
        public int selectHighestSessionFromJoin()
        {
            String SELECT_SESSIONS_SQL_STATEMENT =
                "SELECT u.session " +
                "FROM sessions s " +
                "INNER JOIN urls u " +
                "ON s.lastSessionID = u.session " +
                "GROUP BY session " +
                "ORDER BY session";

            int status = ApmetrixConstants.SQLCE_ERROR;

            SqlCeCommand cmd = new SqlCeCommand(SELECT_SESSIONS_SQL_STATEMENT, connection);
            SqlCeDataReader reader;

            cmd.Prepare();

            reader = cmd.ExecuteReader();

            if (reader != null)
            {
                while (reader.Read())
                {
                    highestSession = reader.GetInt32(0);
                    status = ApmetrixConstants.SQLCE_OK;
                }
            }
            else
            {
                highestSession = 0;
                status = ApmetrixConstants.SQLCE_OK;
            }

            reader.Close();

            return status;

        }

        //TIMER FUNCTIONS STUBBED OUT BUT NOT USING RIGHT NOW
        #region Timers
        /**
         * insert or replace record in Timer table
         * @return
         */
        public int insertOrReplaceTimers(String tag, long startTimestamp)
        {
            /*
             * inserting into timers table causes id to be incremented automatically
             * and time to be updated to current timestamp
             */

            string INSERT_TIMERS_TABLE_STATEMENT =
                "INSERT INTO timers (tag, startTS, duration, running)" +
                "VALUES (" + tag + "," + startTimestamp + "," + 0 + "," + TIMER_RUNNING + ")";

            string DELETE_TIMERS_TABLE_FORMAT_SQL_STATEMENT = "DELETE FROM timers WHERE tag = " + tag;

            string table = "timers";

            int status = 0;

            checkConnection();

            SqlCeCommand cmd = connection.CreateCommand();

            //Perform a Select Query to see if the Entry Exists, Then do a Delete/Insert (Replace)
            int result = 0;
            cmd.CommandText = "SELECT * FROM timers WHERE tag = ?";
            cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
            cmd.Prepare();
            cmd.Parameters["p1"].Value = tag;

            //Try to find the entry, but if it doesnt exist our command is null
            //So catch the nullRef and set the result to 0 and just ya know move on with our life
            try
            {
                result = (int)cmd.ExecuteScalar();
            }
            catch (NullReferenceException sqlex)
            {
                result = 0;
            }

            cmd.Parameters.Clear();

            if (result > 0)
            {
                //Ghetto Replace = Delete Then Insert

                sqlDelete(table, "tag", tag, DELETE_TIMERS_TABLE_FORMAT_SQL_STATEMENT, false);

                cmd.CommandText = "INSERT INTO timers (tag, startTS, duration, running) VALUES (?, ?, ?, ?)";

                cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.Int));
                cmd.Parameters.Add(new SqlCeParameter("p3", SqlDbType.Int));
                cmd.Parameters.Add(new SqlCeParameter("p4", SqlDbType.Int));


                cmd.Prepare();

                cmd.Parameters["p1"].Value = tag;
                cmd.Parameters["p2"].Value = startTimestamp;
                cmd.Parameters["p3"].Value = 0;
                cmd.Parameters["p4"].Value = TIMER_RUNNING;

                try
                {
                    cmd.ExecuteNonQuery();
                    status = ApmetrixConstants.SQLCE_OK;

                    if (ApmetrixConstants.LOG_DEBUG)
                    {
                        Console.WriteLine("Row Addaded");
                        Console.WriteLine("SQL Insert: {0}", INSERT_TIMERS_TABLE_STATEMENT);
                    }

                }
                catch (SqlCeException sqlex)
                {
                    status = ApmetrixConstants.SQLCE_ERROR;
                    if (ApmetrixConstants.LOG_DEBUG)
                    {
                        Console.WriteLine("InsertTimers: {0}", sqlex.Message);
                    }
                }

                cmd.Parameters.Clear();
                connection.Close();
            }
            ////If No entry, do an INSERT
            else
            {
                /*
                 * inserting into sessions table causes time to be updated to current timestamp
                 */
                cmd.CommandText = "INSERT INTO timers (tag, startTS, duration, running) VALUES (?, ?, ?, ?)";

                cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.Int));
                cmd.Parameters.Add(new SqlCeParameter("p3", SqlDbType.Int));
                cmd.Parameters.Add(new SqlCeParameter("p4", SqlDbType.Int));


                cmd.Prepare();

                cmd.Parameters["p1"].Value = tag;
                cmd.Parameters["p2"].Value = startTimestamp;
                cmd.Parameters["p3"].Value = 0;
                cmd.Parameters["p4"].Value = TIMER_RUNNING;

                try
                {
                    cmd.ExecuteNonQuery();
                    status = ApmetrixConstants.SQLCE_OK;

                    if (ApmetrixConstants.LOG_DEBUG)
                    {
                        Console.WriteLine("Row Addaded");
                        Console.WriteLine("SQL Insert: {0}", INSERT_TIMERS_TABLE_STATEMENT);
                    }

                }
                catch (SqlCeException sqlex)
                {
                    status = ApmetrixConstants.SQLCE_ERROR;
                    if (ApmetrixConstants.LOG_DEBUG)
                    {
                        Console.WriteLine("InsertTimers: {0}", sqlex.Message);
                    }
                }

                cmd.Parameters.Clear();
                connection.Close();
            }


            return status;
        }


        /**
         * update records in Timer table
         * @return status
         */
        public int updateTimersForPause(long pauseTime)
        {

            ////		final String UPDATE_TIMERS_TABLE_FORMAT_SQL_STATEMENT =
            ////				"UPDATE timers " +
            ////				"SET duration = %d - startTS + duration, " +
            ////				"SET running = %d " +
            ////				"WHERE running = %d";

            ////Select all the timers, store them in a reader
            ////Cycle through the reader updating each timer in the reader

            //int status = 0;

            //checkConnection();

            //SqlCeCommand cmd = connection.CreateCommand();
            //SqlCeDataReader reader;
            ////Perform a Select Query to get all Timers
            //int result = 0;
            //cmd.CommandText = "SELECT * FROM timers";
            //cmd.Prepare();

            //reader = cmd.ExecuteReader();

            //while (reader.Read())
            //{
            //    string tag = reader.GetString(1);
            //    long startTime = reader.GetDateTime(2).Ticks;
            //    long duration = reader.GetInt32(3);

            //    long newDur = pauseTime - startTime + duration;
            //    long run = TIMER_STOPPED;

            //}

            //cmd.CommandText = "UPDATE timers SET duration = ?, SET running";

            //cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
            //cmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.Int));


            //cmd.Prepare();

            //cmd.Parameters["p1"].Value = lastRequestTimestamp;
            //cmd.Parameters["p2"].Value = sessionID;

            //try
            //{
            //    cmd.ExecuteNonQuery();
            //    status = ApmetrixConstants.SQLCE_OK;
            //    if (ApmetrixConstants.LOG_DEBUG)
            //    {
            //        Console.WriteLine("Row Updated");
            //        Console.WriteLine("SQL UPDATE: {0}", UPDATE_SESSIONS_TABLE_FORMAT_SQL_STATEMENT);
            //    }

            //}
            //catch (SqlCeException sqlex)
            //{
            //    status = ApmetrixConstants.SQLCE_ERROR;
            //    if (ApmetrixConstants.LOG_DEBUG)
            //    {
            //        Console.WriteLine("SQL FAILED UPDATE: {0}", UPDATE_SESSIONS_TABLE_FORMAT_SQL_STATEMENT);
            //        Console.WriteLine("UPDATE SESSION TS: {0}", sqlex.Message);
            //    }
            //}

            //cmd.Parameters.Clear();

            //return status;

            return 0;
        }

        /**
        * delete record from Timers table
        * @return
        */
        public int deleteFromTimers(String tag)
        {

            //String table = "timers";
            //String whereClause = "tag=?";
            //String[] whereArgs = { String.valueOf(tag) };

            //int status = SQLITE_ERROR;

            //if (db.delete(table, whereClause, whereArgs) != 0)
            //{
            //    status = SQLITE_OK;
            //}
            return 0;
        }

        public int clearTimers()
        {
        //    String table = "timers";
        //    String whereClause = "1";	// to delete all rows and get a count (null deletes all rows but returns no count)
        //    String[] whereArgs = null;

        //    int status = SQLITE_ERROR;

        //    if (db.delete(table, whereClause, whereArgs) != 0)
        //    {
        //        status = SQLITE_OK;
        //    }
            return 0;
        }

        #endregion

        //**********************************************************

        /**
             * getter for sessions table lastSessionID value
             * @return lastSessionID
             */
        public int getLastSessionID()
        {
            return lastSessionID;
        }

        /**
         * getter for sessions table lastSessionLastTS value
         * @return lastSessionLastTS
         */
        public long getLastSessionLastTS()
        {
            return lastSessionLastTS;
        }

        /**
         * getter for sessions table initialSessionTS value
         * @return initialSessionTS
         */
        public long getInitialSessionTS()
        {
            return initialSessionTS;
        }

        /**
         * getter for urls table id value
         * @return id
         */
        public int getID()
        {
            return id;
        }

        /**
         * getter for urls table id column value
         * @param SqlCeDataReader reader with table query results
         * @return id column value for the row specified by the cursor
         */
        public int getID(SqlCeDataReader reader)
        {
            return reader.GetInt32(reader.GetOrdinal("_id"));
        }

        /**
         * getter for urls table url value
         * @return url
         */
        public string getUrl()
        {
            return url;
        }

        /**
         * getter for urls table url column value
         * @param SqlCeDataReader reader with table query results
         * @return url column value for the row specified by the cursor
         */
        public string getUrl(SqlCeDataReader reader)
        {
            return reader.GetString(reader.GetOrdinal("url"));// cursor.getString(cursor.getColumnIndex("url"));
        }

        /**
         * getter for urls table session value
         * @return session
         */
        public int getSession()
        {
            return session;
        }

        /**
         * getter for joined table session value
         * @return highestSession
         */
        public int getHighestSession()
        {
            return highestSession;
        }

        /**
         * getter for sessions table session value
         * @return lowestSession
         */
        public int getLowestSession()
        {
            return lowestSession;
        }

        /**
         * getter for number of entries in sessions table
         * @return sessionCount
         */
        public int getSessionCount()
        {
            return sessionCount;
        }

        /**
         * getter for number of entries in urls table
         * @return packetCount
         */
        public int getPacketCount()
        {
            return packetCount;
        }

        /**
         * getter for urls cursor object
         * @return cursor object
         */
        public SqlCeDataReader getUrlsReader()
        {
            return urlReader;
        }

        /**
         * getter for timer duration
         * @return duration
         */
        public long getEventDuration()
        {
            return eventDuration;
        }



        /// <summary>
        /// Creates the database by setting up the appropriate tables
        /// Returns a new connection to that database
        /// Handles correctly if the database already exists
        /// </summary>
        /// <returns>New Connection to the database</returns>
        private SqlCeConnection SQLCEHelper()
        {
            //create the tables
            //if we ever get a native error of zero, tables exist and we just want to 
            //immediately open the connection and return a connection
            //otherwise add the tables, open the connection and return a new connection to the db
            SqlCeConnection newCon = new SqlCeConnection("Data Source = " + source);
            int tableCount = 0;

            if (newCon.State == ConnectionState.Closed)
                newCon.Open();

            //dropTables();

            SqlCeCommand cmd;

            string CREATE_SESSIONS_TABLE_SQL_STATEMENT =
                  "CREATE TABLE sessions (" +
                  "id INT IDENTITY not null PRIMARY KEY," +
                  "lastSessionID INT default (0) not null, " +
                  "lastSessionLastTS BigInt NOT NULL, " +				// (e.g. 2012-07-19 08:07:20)
                  "initialSessionTS BigInt NOT NULL, " +				// (e.g. 2012-07-19 08:07:20)
                  "time BigInt DEFAULT " + ApmetrixConstants.GetCurrentMilli() +" NOT NULL)";	// (e.g. 2012-07-19 08:07:20)


            cmd = new SqlCeCommand(CREATE_SESSIONS_TABLE_SQL_STATEMENT, newCon);

            try
            {
                cmd.ExecuteNonQuery();
                Console.WriteLine("Table Created");
            }
            catch (SqlCeException sqlex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0} {1} CREATE_SESSIONS_TABLE_SQL_STATEMENT", sqlex.NativeError, sqlex.Message);
            }
            catch (Exception ex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0} Oh ex it", ex.Message);
            }

            string CREATE_URLS_TABLE_SQL_STATEMENT =
                  "CREATE TABLE urls (" +
                  "_id INT IDENTITY NOT NULL PRIMARY KEY, " +
                  "url ntext NOT NULL, " +
                  "time BigInt DEFAULT " + ApmetrixConstants.GetCurrentMilli() +" NOT NULL," +	// (e.g. 2012-07-19 08:07:20)
                  "session INT NOT NULL)";


            cmd = new SqlCeCommand(CREATE_URLS_TABLE_SQL_STATEMENT, newCon);

            try
            {
                cmd.ExecuteNonQuery();
                Console.WriteLine("Table Created");
            }
            catch (SqlCeException sqlex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0} {1} CREATE_URLS_TABLE_SQL_STATEMENT", sqlex.NativeError, sqlex.Message);
            }
            catch (Exception ex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0} Oh ex it", ex.Message);
            }


            string CREATE_TIMERS_TABLE_SQL_STATEMENT =
                  "CREATE TABLE timers (" +
                  "_id INT IDENTITY not null PRIMARY KEY, " +
                  "tag nvarchar (256) not null, " +
                  "startTS BigInt NOT NULL, " +							// (e.g. 2012-07-19 08:07:20)
                  "duration INT NOT NULL, " +
                  "running INT NOT NULL, " +
                  "time BigInt DEFAULT " + ApmetrixConstants.GetCurrentMilli() +" NOT NULL)";	// (e.g. 2012-07-19 08:07:20)


            cmd = new SqlCeCommand(CREATE_TIMERS_TABLE_SQL_STATEMENT, newCon);

            try
            {
                cmd.ExecuteNonQuery();
                Console.WriteLine("Table Created");
            }
            catch (SqlCeException sqlex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0} {1} CREATE_TIMERS_TABLE_SQL_STATEMENT", sqlex.NativeError, sqlex.Message);
            }
            catch (Exception ex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0} Oh ex it", ex.Message);
            }

            //***********IGNORNING DROP TABLES FOR NOW**************
            return newCon;

        }

        private void dropTables()
        {
            SqlCeConnection newCon = new SqlCeConnection("Data Source = " + source);
           

            if (newCon.State == ConnectionState.Closed)
                newCon.Open();

            SqlCeCommand cmd;

            string DROP_SESSIONS_TABLE_SQL_STATEMENT =
                  "DROP TABLE sessions";

            string DROP_URLS_TABLE_SQL_STATEMENT =
                  "DROP TABLE urls";

            string DROP_TIMERS_TABLE_SQL_STATEMENT =
                  "DROP TABLE timers";

            cmd = new SqlCeCommand(DROP_SESSIONS_TABLE_SQL_STATEMENT, newCon);

            try
            {
                cmd.ExecuteNonQuery();
                Console.WriteLine("SESSIONS Table DROPED");
            }
            catch (SqlCeException sqlex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0} {1} Oh CREATE_TIMERS_TABLE_SQL_STATEMENT it", sqlex.NativeError, sqlex.Message);
            }
            catch (Exception ex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0} Oh ex it", ex.Message);
            }

            cmd = new SqlCeCommand(DROP_URLS_TABLE_SQL_STATEMENT, newCon);

            try
            {
                cmd.ExecuteNonQuery();
                Console.WriteLine("URLS Table DROPED");
            }
            catch (SqlCeException sqlex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0} {1} Oh CREATE_TIMERS_TABLE_SQL_STATEMENT it", sqlex.NativeError, sqlex.Message);
            }
            catch (Exception ex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0} Oh ex it", ex.Message);
            }

            cmd = new SqlCeCommand(DROP_TIMERS_TABLE_SQL_STATEMENT, newCon);

            try
            {
                cmd.ExecuteNonQuery();
                Console.WriteLine("TIMERS Table DROPED");
            }
            catch (SqlCeException sqlex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0} {1} Oh CREATE_TIMERS_TABLE_SQL_STATEMENT it", sqlex.NativeError, sqlex.Message);
            }
            catch (Exception ex)
            {
                if (ApmetrixConstants.LOG_DEBUG)
                    Console.WriteLine("{0} Oh ex it", ex.Message);
            }

        }

    }
}
